package daoJDBC;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import connection.ConnectionDB;
import entityJDBC.Candidate;

public class JDBCCandidateDAO /*implements ICandidateDAO */{

	private ConnectionDB con = ConnectionDB.getInstance();

	
	public void insertCandidate(Candidate candidate) {
		
		FileInputStream fis = null;
		
		try {
			
			String sql = "insert into javatr.candidate(firstName,lastName,"

					+"homeNumber,cellNumber,email,address,relocation,cvUpload, idPosition)"
					+ "values(?,?,?,?,?,?,?,?,?)";
			
			PreparedStatement preparedStatement = con.getConnection().prepareStatement(sql);
			preparedStatement.setString(1, candidate.getFirstName());
			preparedStatement.setString(2, candidate.getLastName());
			preparedStatement.setString  (3, candidate.getHomeNumber());
			preparedStatement.setString  (4, candidate.getCellNumber());
			preparedStatement.setString  (5, candidate.getEmail());
			preparedStatement.setString  (6, candidate.getAddress());
			preparedStatement.setByte(7, candidate.getRelocation());
			preparedStatement.setInt(9, candidate.getIdPosition());
			
			File file = new File(candidate.getCV());
            fis = new FileInputStream(file);
            preparedStatement.setBinaryStream(8, fis, (int) file.length());
            preparedStatement.executeUpdate();
            
			

		} catch (SQLException ex) {

			ex.printStackTrace();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}finally {
            if (fis != null) {
                try {
					fis.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
            }
        }

		

	}

	public void updateCandidate(Candidate candidate) {


		FileInputStream fis = null;



		try {

			String sql = "update javatr.candidate set firstName=? , lastName=?, homeNumber=?,"
						+ "cellNumber=?, email=?, address=?, relocation=?,"
					    + "cvUpload=?, idPosition=? where idCandidate=?";


			PreparedStatement preparedStatement = con.getConnection()
					.prepareStatement(sql);

			preparedStatement.setString(1, candidate.getFirstName());
			preparedStatement.setString(2, candidate.getLastName());
			preparedStatement.setString(3, candidate.getHomeNumber());
			preparedStatement.setString(4, candidate.getCellNumber());
			preparedStatement.setString(5, candidate.getEmail());
			preparedStatement.setString(6, candidate.getAddress());
			preparedStatement.setByte(7, candidate.getRelocation());
			File file = new File(candidate.getCV());
            fis = new FileInputStream(file);
            preparedStatement.setBinaryStream(8, fis, (int) file.length());
            
			preparedStatement.setInt(9, candidate.getIdPosition());
			preparedStatement.setInt(10, candidate.getIdCanditate());

			preparedStatement.executeUpdate();
		} catch (SQLException ex) {

			ex.printStackTrace();
		} catch (FileNotFoundException e) {
			e.printStackTrace();

		}

	}

	public void deleteCandidate(int idCandidate) {
		try {
			
			String sql = "delete from javatr.candidati where idCandidate=?";
			PreparedStatement preparedStatement = con.getConnection()
					.prepareStatement(sql);

			preparedStatement.setInt(1, idCandidate);

			preparedStatement.executeUpdate();
		} catch (SQLException ex) {

			ex.printStackTrace();

			System.out.print(ex.toString());
		} finally {
			con.disconectDatabase();

		}
	}

	public Candidate findById(int idCandidate) {
		Candidate candidate;

		try {
			String sql = "select * from candidate where idcandidate="+idCandidate;

			Statement stmt = con.getConnection().createStatement();
			ResultSet result = stmt.executeQuery(sql);
			while (result.next()) {
				String firstName = result.getString("firstName");
				String lastName = result.getString("lastName");
				String homeNumber = result.getString("homeNumber");
				String cellNumber = result.getString("cellNumber");
				String email = result.getString("email");
				String adress = result.getString("address");
				byte relocation = result.getByte("relocation");
				int idPosition = result.getInt("idPosition");
				int idCan = result.getInt("idCandidate");
				InputStream is = result.getBinaryStream(7);
				byte[] buffer = new byte[256];
				String CV = null;
				while (is.read(buffer) > 0) {
                    CV+=buffer;
                }
				candidate = new Candidate(idCan, firstName, lastName, homeNumber, cellNumber, email, adress, CV	, relocation, idPosition);

				return candidate;
			}

				
			}catch(SQLException ex){
				ex.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			}

		return null;
	}

	public List<Candidate> findAll() {
		List<Candidate> candidatesList = new ArrayList<Candidate>();
		try{
			String    sql       = "select * from javatr.candidate where idCandidate=?";
			Statement stmt = con.getConnection().createStatement();
			ResultSet result = stmt.executeQuery(sql);
			while(result.next()){
				String firstName = result.getString("firstName");
				String lastName = result.getString("LastName");
				String homeNumber = result.getString("homeNumber");
				String cellNumber = result.getString("cellNumber");
				String email = result.getString("email");
				String adress = result.getString("address");
				InputStream is = result.getBinaryStream(7);
				byte[] buffer = new byte[256];
				String CV = null;
				while (is.read(buffer) > 0) {
                    CV+=buffer;
                }
				byte relocation = result.getByte("relocation");
				int idPosition = result.getInt("idPosition");
				int idCan = result.getInt("idCandidate");
				candidatesList.add(new Candidate(idCan, firstName, lastName, homeNumber, cellNumber, email, adress, CV, relocation, idPosition));
				
			}
				return candidatesList;
			}catch(SQLException ex){
				ex.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			}
			
		return null;
	}

}
